#include <boost/asio/ssl.hpp>
#include <boost/mysql.hpp>
#include <boost/mysql/common_server_errc.hpp>
#include <boost/mysql/error_categories.hpp>
#include <boost/mysql/error_code.hpp>
#include <boost/mysql/error_with_diagnostics.hpp>
#include <boost/mysql/tcp_ssl.hpp>
#include <boost/smart_ptr/make_shared_object.hpp>
#include <boost/system/detail/error_code.hpp>
#include <data/UserInfo.pb.h>
#include <iostream>
#include <ostream>
#include <vector>

// 创建 MySQL 连接
using namespace proto;
using namespace std;
boost::asio::io_context ctx;
static boost::asio::ssl::context ssl_ctx(boost::asio::ssl::context::tls_client);
static boost::mysql::tcp_ssl_connection* mysqlconn;

bool reconnect();

void do_exception(boost::mysql::error_with_diagnostics& e, std::string func) {
    auto code = e.code();
    auto dia  = e.get_diagnostics();
    std::cerr << __func__ << e.what() << " server message: " << dia.server_message() << std::endl;
    switch (code.value()) {
        case static_cast<int>(boost::mysql::common_server_errc::er_signal_exception):

            break;
        case 10054:  // 远程主机强迫关闭了一个现有的连接。
        case 10009:  // 提供的文件句柄无效。
        case 10060:  // 由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败。
        case 10065:  // 套接字操作尝试一个无法连接的主机。
            std::cerr << "reconnect mysql :" << (reconnect() ? "Succsess" : "Fail") << std::endl;

        default:
            break;
    }
}

void do_exception(const std::exception& e, std::string func) {
    std::cerr << "MysqlUtils::" << func << "() " << e.what() << std::endl;
}

bool reconnect() {
    if (mysqlconn != nullptr) {
        delete mysqlconn;
    }

    mysqlconn = new boost::mysql::tcp_ssl_connection(ctx, ssl_ctx);
    // Resolve the hostname to get a collection of endpoints
    boost::asio::ip::tcp::resolver resolver(ctx.get_executor());

    auto endpoints = resolver.resolve("112.74.82.226", boost::mysql::default_port_string);

    // The username, password and database to use
    boost::mysql::handshake_params params(
        "root",       // username
        "hansi",      // password
        "boost_chat"  // database
    );
    // Connect to the server using the first endpoint returned by the resolver
    try {
        mysqlconn->connect(*endpoints.begin(), params);
        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        std::cerr << e.what() << '\n';
    }
    catch (const std::exception& e) {
        std::cerr << e.what() << '\n';
    }

    return false;
}
bool getAcount(data::Account& account) {
    try {
        static boost::mysql::statement stmt =
            // mysqlconn->prepare_statement("SELECT userId, account, password FROM
            // `account` where userId = ? or account = ?");
            mysqlconn->prepare_statement(
                "CALL Login(?,?, @out_userId, @out_account, @out_password)");

        boost::mysql::results r;
        mysqlconn->execute_statement(
            stmt, std::make_tuple(account.userid(), account.account()), r);

        if (!r.rows().empty()) {
            auto row = r.rows()[0];
            // account  = {row[0].get_string(), row[1].get_string(), row[2].get_string()};
            account.set_userid(row[0].get_string());
            account.set_account(row[1].get_string());
            account.set_password(row[2].get_string());
            return true;
        }
        boost::mysql::results result;
        mysqlconn->query("select @out_userId, @out_account, @out_password", result);

        // std::cout<< "meta:" << result.meta() << std::endl;
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            auto row = result.rows()[0];
            // account  = {row[0].get_string(), row[1].get_string(), row[2].get_string()};
            account.set_userid(row[0].get_string());
            account.set_account(row[1].get_string());
            account.set_password(row[2].get_string());
            return true;
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        auto code = e.code();
        std::cerr << __func__ << e.what() << "code:" << code << std::endl;
        std::cerr << "reconnect mysql :" << (reconnect() ? "Succsess" : "Fail") << std::endl;
    }
    catch (const std::exception& e) {
        std::cerr << __func__ << e.what() << std::endl;
    }

    return false;
}

bool createRoom(data::ChatRoomInfo& info) {
    try {
        static boost::mysql::statement stmt =
            mysqlconn->prepare_statement(R"(CALL CreateRoom(?,?,?,?, @room_id))");

        boost::mysql::results r;
        mysqlconn->execute_statement(
            stmt,
            std::make_tuple(info.userid(), info.name(), info.sign(), info.headshot()),
            r);

        boost::mysql::results result;
        mysqlconn->query("select @room_id", result);
        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            auto row = result.rows()[0];
            info.set_roomid(row[0].get_string());
            return true;
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        auto code = e.code();
        std::cerr << __func__ << ": " << e.what() << "code:" << code << std::endl;
        // std::cerr << "reconnect mysql :" << (reconnect() ? "Succsess" : "Fail") << std::endl;
        auto dia = e.get_diagnostics();
        std::cerr << dia.server_message() << std::endl;
        std::cerr << std::endl;

        auto& category = boost::mysql::get_mysql_server_category();
        std::cout << category.name() << std::endl;
        std::cout << code.message() << std::endl;

        switch (boost::mysql::common_server_errc(code.value())) {
            case boost::mysql::common_server_errc::er_signal_exception:

                break;

            default:
                break;
        }
    }
    catch (const std::exception& e) {
        std::cerr << __func__ << e.what() << std::endl;
    }

    return false;
}

int searchAutor(std::string text, std::vector<data::UserInfo>& users) {
    // SELECT * FROM `userinfo` WHERE account LIKE "hans" or userId LIKE "han" or `name` LIKE "%斯%";
    data::UserInfo userInfo = {};
    try {
        static std::string sql = R"(
SELECT userId, account, name, sign, age, sex, address, phone, email, headshot FROM `userinfo` 
WHERE account LIKE ? or userId LIKE ? or `name` LIKE ?;
        )";

        text.insert(text.begin(), '%');
        text.push_back('%');

        cout << text << endl;
        boost::mysql::statement stmt =
            mysqlconn->prepare_statement(sql);

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(text, text, text), result);

        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                userInfo.set_userid(row[0].is_null() ? "" : row[0].get_string());
                userInfo.set_account(row[1].is_null() ? "" : row[1].get_string());
                userInfo.set_name(row[2].is_null() ? "" : row[2].get_string());
                userInfo.set_sign(row[3].is_null() ? "" : row[3].get_string());
                userInfo.set_age(row[4].is_null() ? 0 : row[4].get_int64());
                userInfo.set_sex(row[5].is_null() ? "0" : row[5].get_string());
                userInfo.set_address(row[6].is_null() ? "" : row[6].get_string());
                userInfo.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
                userInfo.set_email(row[8].is_null() ? "" : row[8].get_string());
                userInfo.set_headshot(row[9].is_null() ? "" : row[9].get_string());
                users.push_back(userInfo);
            }
            return users.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
    return users.size();
}

int searchGroup(std::string text, std::vector<data::ChatRoomInfo>& groups) {
    data::ChatRoomInfo info = {};
    try {
        static std::string sql = R"(
SELECT groupId, userId, name, sign, headshot 
FROM groupinfo
WHERE groupId LIKE ? OR `name` LIKE ? ;
        )";

        text.insert(text.begin(), '%');
        text.push_back('%');
        boost::mysql::statement stmt =
            mysqlconn->prepare_statement(sql);

        boost::mysql::results result;
        mysqlconn->execute_statement(stmt, std::make_tuple(text, text), result);

        // 如果结果为空表示用户名错误
        if (!result.rows().empty()) {
            const auto& rows = result.rows();
            for (auto&& row : rows) {
                info.set_roomid(row[0].is_null() ? "" : row[0].get_string());
                info.set_userid(row[1].is_null() ? "" : row[1].get_string());
                info.set_name(row[2].is_null() ? "" : row[2].get_string());
                info.set_sign(row[3].is_null() ? "" : row[3].get_string());
                info.set_headshot(row[4].is_null() ? "" : row[4].get_string());
                groups.push_back(info);
            }
            return groups.size();
        }
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
    return groups.size();
}

void addFriends(std::string userid, std::string friendsId) {
    try {
        boost::mysql::results r;
        boost::mysql::statement stmt_register =
            mysqlconn->prepare_statement("CALL AddFriends(?, ?)");

        mysqlconn->execute_statement(
            stmt_register,
            std::make_tuple(userid, friendsId), r);
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
}

void delFriends(std::string userid, std::string friendsId) {
    try {
        boost::mysql::results r;
        boost::mysql::statement stmt_register =
            mysqlconn->prepare_statement("CALL DelFriends(?, ?)");

        mysqlconn->execute_statement(
            stmt_register,
            std::make_tuple(userid, friendsId), r);
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }
}

bool registerUser(data::UserInfo& user, data::Account& account) {
    try {
        boost::mysql::results r;
        boost::mysql::results result;
        boost::mysql::statement stmt_register =
            mysqlconn->prepare_statement(R"(CALL RegisterUser(?, ?, ?,
            @out_userId,@out_account,@out_name,@out_sign,@out_age,@out_sex,@out_address,@out_phone,@out_email,@out_headshot,@out_birthday
        ))");

        mysqlconn->execute_statement(
            stmt_register,
            std::make_tuple(account.account(), account.password(), user.name()), r);

        mysqlconn->query("SELECT @out_userId,@out_account,@out_name,@out_sign,@out_age,@out_sex,@out_address,@out_phone,@out_email,@out_headshot,@out_birthday", result);
        auto row = result.rows()[0];

        if (row[0].is_null())
        {
            std::cout << "null" << std::endl;
        }else{
            std::cout << row[0].get_string() << std::endl;
            std::cout << row[0].get_string() << std::endl;
        }
        user.set_userid(row[0].is_null() ? "" : row[0].get_string());
        user.set_account(row[1].is_null() ? "" : row[1].get_string());
        user.set_name(row[2].is_null() ? "" : row[2].get_string());
        user.set_sign(row[3].is_null() ? "" : row[3].get_string());
        user.set_age(row[4].is_null() ? 0 : row[4].get_int64());
        user.set_sex(row[5].is_null() ? "0" : row[5].get_string());
        user.set_address(row[6].is_null() ? "" : row[6].get_string());
        user.set_phonenumber(row[7].is_null() ? "" : row[7].get_string());
        user.set_email(row[8].is_null() ? "" : row[8].get_string());
        user.set_headshot(row[9].is_null() ? "" : row[9].get_string());

        account.set_userid(user.userid());
        return true;
    }
    catch (boost::mysql::error_with_diagnostics& e) {
        do_exception(e, __func__);
    }
    catch (const std::exception& e) {
        do_exception(e, __func__);
    }

    return false;
}
#include <system/System.h>

int main() {
    sys::InitConsole();
    data::Account account;
    account.set_account("hansi");

    try {
        reconnect();

        // // 执行存储过程
        if (0) {
            if (getAcount(account)) {
                std::cout << account.DebugString() << std::endl;
            }
        }

        if (0) {
            data::ChatRoomInfo info;
            info.set_name("测试");
            info.set_userid("100001");
            info.set_sign("sdfasdfasdfasd");
            createRoom(info);
        }

        if (0) {
            std::vector<data::UserInfo> users;

            int size = searchAutor("斯", users);
            cout << "searchAuthor:" << endl;

            for (auto&& i : users) {
                std::cout << i.DebugString() << std::endl;
            }
        }
        if (0) {
            std::vector<data::ChatRoomInfo> rooms;

            int size = searchGroup("100", rooms);
            cout << "searchAuthor:" << endl;

            for (auto&& i : rooms) {
                std::cout << i.DebugString() << std::endl;
            }
        }

        if (0) {
            addFriends("100004", "100001");
        }

        if (1) {
            data::Account account;
            data::UserInfo user;

            account.set_account("sga5");
            account.set_password("hansi");
            user.set_name(account.account());
            registerUser(user, account);
            std::cout << user.DebugString() << std::endl;
        }

        ctx.run();

        // 关闭连接
        mysqlconn->close();
    }
    catch (const boost::mysql::error_with_diagnostics& err) {
        std::cerr << "Connection error: " << err.what() << std::endl;
    }

    return 0;
}
